<?php
/**
 * Created by PhpStorm.
 * User: Steven
 * Date: 2017/5/24
 */

namespace app\student\model;


use think\Db;
use think\Model;

class StudentModel extends Model
{
    public function __construct($data = [])
    {
        parent::__construct($data);
    }

    /**
     * 获取学生列表信息
     * @param array $filter
     * @param array $order
     * @param int $limit
     * @param $query
     * @return \think\Paginator
     */
    public function getStudents($filter=[], $order=[], $limit=10, $query)
    {
        //默认条件
        $where = [
            's.delete_time' => ['=', 0], //未删除
        ];
        if(!empty($filter) && is_array($filter)){
            $where = array_merge($where,$filter);
        }

        //默认排序
        $sort = ['s.id'=>'desc'];
//        $orderby="convert(`s.name` USING gbk) COLLATE gbk_chinese_ci";
        if(!empty($order) && is_array($order)){
            $sort = array_merge($order,$sort);
        }

        //分页url额外参数
        $page_query['query'] = [];
        if(!empty($query) && is_array($query)){
            $page_query['query'] = $query;
        }

        //在读 在读状态
        if(!empty($where['is_read'])) {
            if ($where['is_read'] == 1) {
                unset($where['is_read']);
                $data = $this->alias('s')->distinct('s.id')
                    ->field('s.*,u.id as uid,u.realname,u.user_login as p_name')
                    ->join('user u', 's.u_id=u.id', 'left')
                    ->join('class_student ct', 'ct.student_id=s.id')
                    ->join('class c', 'ct.class_id=c.id')
                    ->join('school sc', 'ct.school_id=sc.id')
                    ->join('course co', 'ct.course_id=co.id')
                    ->join('order o', 'o.class_id=ct.class_id and c.id=o.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                    ->where('o.order_status', ['eq', 2], ['eq', 1], 'or')
                    ->where($where)
                    ->where('c.class_status', 1)
                    ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                    ->paginate($limit, false, $page_query);

            } elseif ($where['is_read'] == 2) {
                unset($where['is_read']);
                $data = $this->alias('s')->distinct('s.id')
                    ->field('s.*,u.id as uid,u.realname,u.user_login as p_name')
                    ->join('user u', 's.u_id=u.id', 'left')
                    ->join('class_student ct', 'ct.student_id=s.id')
                    ->join('class c', 'ct.class_id=c.id')
                    ->join('school sc', 'ct.school_id=sc.id')
                    ->join('course co', 'ct.course_id=co.id')
                    ->join('order o', 'o.class_id=ct.class_id and c.id=o.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                    ->where('o.order_status', ['eq', 2], ['eq', 1], 'or')
                    ->where($where)
                    ->where('c.class_status', ['eq', 2], ['eq', 0], 'or')
                    ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                    ->paginate($limit, false, $page_query);

            }
        }else{

            $data = $this->alias('s')->distinct('s.id')
                ->field('s.*,u.id as uid,u.realname,u.user_login as p_name')
                ->join('user u','s.u_id=u.id','left')
                ->join('class_student ct','ct.student_id=s.id',$type='left')
                ->join('school sc','ct.school_id=sc.id',$type='left')
                ->join('course co','ct.course_id=co.id',$type='left')
                ->where($where)
                ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                ->paginate($limit,false,$page_query);

        }
        foreach($data as $k=>$v)
        {
            $information_sources='';
            $type=Db::name('help_award')->where(['student_id'=>$v['id'],'status'=>2,'delete_time'=>0])->value('type');
            $arr_information_sources=explode(',',$v['information_sources_type']);
            if(in_array(1,$arr_information_sources))
            {
                $information_sources.='学校推荐 ';
            }
            if(in_array(2,$arr_information_sources))
            {
                $information_sources.='<a class="teacher" attr_id="'.$v['id'].'" attr_type="2">老师推荐</a> ';
            }
            if(in_array(3,$arr_information_sources))
            {
                $information_sources.='学校官方 ';
            }
            if(in_array(4,$arr_information_sources))
            {
                $information_sources.='学校微信 ';
            }
            if(in_array(5,$arr_information_sources))
            {
                $information_sources.='新闻媒体 ';
            }
            if(in_array(6,$arr_information_sources))
            {
                $information_sources.='信箱广告 ';
            }
            if(in_array(7,$arr_information_sources))
            {
                $information_sources.='<a class="student_student" attr_id="'.$v['id'].'" attr_type="7">同学推荐</a> ';
            }
            if(in_array(8,$arr_information_sources))
            {
                $information_sources.='<a class="friend" attr_id="'.$v['id'].'" attr_type="8">朋友介绍</a> ';

            }
            if(in_array(9,$arr_information_sources))
            {
                $information_sources.='<a class="other" attr_id="'.$v['id'].'" attr_type="9">其他</a> ';

            }
            $data[$k]['information_sources']=$information_sources;
            if(empty($type))
            {
                $type='无';
            }else if($type==1)
            {
                $type='重大疾病';
            }else if($type==2)
            {
                $type='单亲';
            }else if($type==3)
            {
                $type='其他';
            }
             $data[$k]['award_type']=$type;
        }
        return $data;
    }

    /**
     * @param array $filter
     * @param array $order
     * @param int $limit
     * @param $query
     * @return \think\Paginator
     * 导出execel
     */
    public function getStudents1($filter=[], $order=[])
    {
        //默认条件
        $where = [
            's.delete_time' => ['=', 0], //未删除
        ];
        if(!empty($filter) && is_array($filter)){
            $where = array_merge($where,$filter);
        }

        //默认排序
        $sort = ['s.id'=>'desc'];
//        $orderby="convert(`s.name` USING gbk) COLLATE gbk_chinese_ci";
        if(!empty($order) && is_array($order)){
            $sort = array_merge($order,$sort);
        }

          $time=time();
        if(!empty($where['is_read'])) {
            if($where['is_read']==1)
            {

                      unset($where['is_read']);
                          $id = $this->alias('s')->distinct('s.id')
                            ->join('class_student ct','ct.student_id=s.id')
                            ->join('class c','ct.class_id=c.id')
                            ->join('order o','o.class_id=ct.class_id and c.id=o.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                            ->where('o.order_status', ['eq', 2], ['eq', 1], 'or')
                            ->where($where)
                            ->where('c.class_status',1)
                            ->column('s.id');
                            $data = $this->alias('s')->distinct('s.id')
                                ->field('s.*,u.id as uid,u.realname,
                                        u.user_login as p_name,
                                        co.course_name,co.course_hour,
                                        ct.insert_class_time,ct.class_id,o.order_status,
                                        c.class_status,c.name cname,o.bill_status,o.pay_amount,o.amount
                                       ')
                                ->join('user u','s.u_id=u.id','left')
                                ->join('class_student ct','ct.student_id=s.id',$type='left')
                                ->join('class c','ct.class_id=c.id')
                                ->join('school sc','ct.school_id=sc.id',$type='left')
                                ->join('course co','ct.course_id=co.id ',$type='left')
                                ->join('order o','o.class_id=ct.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                                ->where($where)
                                ->where('s.id','in',$id)
                                ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                                ->select();
               }else{
                unset($where['is_read']);
                $id = $this->alias('s')->distinct('s.id')
                    ->join('class_student ct','ct.student_id=s.id')
                    ->join('class c','ct.class_id=c.id')
                    ->join('order o','o.class_id=ct.class_id and c.id=o.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                    ->where('o.order_status', ['eq', 2], ['eq', 1], 'or')
                    ->where($where)
                    ->where('c.class_status',['eq', 2], ['eq', 0], 'or')
                    ->column('s.id');
                $data = $this->alias('s')->distinct('s.id')
                    ->field('s.*,u.id as uid,u.realname,
                                        u.user_login as p_name,
                                        co.course_name,co.course_hour,
                                        ct.insert_class_time,ct.class_id,o.order_status,
                                        c.class_status,c.name cname,o.bill_status,o.pay_amount,o.amount
                                       ')
                    ->join('user u','s.u_id=u.id','left')
                    ->join('class_student ct','ct.student_id=s.id',$type='left')
                    ->join('class c','ct.class_id=c.id')
                    ->join('school sc','ct.school_id=sc.id',$type='left')
                    ->join('course co','ct.course_id=co.id ',$type='left')
                    ->join('order o','o.class_id=ct.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                    ->where($where)
                    ->where('s.id','in',$id)
                    ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                    ->select();
            }
        }
        else{


            $data = $this->alias('s')->distinct('s.id')
                ->field('s.*,u.id as uid,u.realname,
            u.user_login as p_name,
            co.course_name,co.course_hour,
            ct.insert_class_time,ct.class_id,o.order_status,
            c.class_status,c.name cname,o.bill_status,o.pay_amount,o.amount
           ')
                ->join('user u','s.u_id=u.id','left')
                ->join('class_student ct','ct.student_id=s.id',$type='left')
                ->join('class c','ct.class_id=c.id')
                ->join('school sc','ct.school_id=sc.id',$type='left')
                ->join('course co','ct.course_id=co.id ',$type='left')
                ->join('order o','o.class_id=ct.class_id and o.student_id=s.id and o.student_id=ct.student_id',$type='left')
                ->where($where)
                ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                ->select();
        }

//dump($data);
//        exit();
        foreach($data as $k=>$v)
        {
            if(!is_null($v['order_status']))
            {
                if($v['order_status']==0  || $v['order_status']==3)
                {
                    unset($data[$k]);
                }
            }
        }
            if(!$data->isEmpty())
            {
                $data=$data->toArray();
                foreach($data as $k=>$v) {
                    $type = Db::name('help_award')->where(['student_id' => $v['id'], 'status' => 2, 'delete_time' => 0])->value('type');

                    if(empty($type))
                    {
                        $type='无';
                    }else if($type==1)
                    {
                        $type='重大疾病';
                    }else if($type==2)
                    {
                        $type='单亲';
                    }else if($type==3)
                    {
                        $type='其他';
                    }
                    $data[$k]['award_type'] = $type;
                    //课程进度
                    $progress = 0;
                    $course_scheme = Db::name('course_schedule')->where(['class_id' => $v['class_id']])->select();
                    if (!$course_scheme->isEmpty()) {
                        $course_scheme = $course_scheme->toArray();
                        $first_lesson_day = $course_scheme[0]['date'];
                        $date_length = count($course_scheme);
                        $end_lesson_day = $course_scheme[$date_length - 1]['date'];


                        if ($first_lesson_day >=$time) {
                            $progress = 0;
                        }elseif ($time > $first_lesson_day && $time < $end_lesson_day) {
                            $progress = 0;
                            foreach ($course_scheme as $kk => $val) {
                                if (!empty($course_scheme[$kk + 1]['date'])) { //一般都是存在的2课时以上的
                                    if ($time > $course_scheme[$kk]['date'] && $time < $course_scheme[$kk + 1]['date']) {
                                        $progress = $course_scheme[$kk]['schedule']; //当前课时
                                    } elseif ($time <= $course_scheme[0]['date']) { //小于开课时间
                                        $progress = 0; //当前课时
                                    } else if ($time >= $course_scheme[$date_length - 1]['date']) {  //已经结课
                                        $progress = $date_length; //当前课时
                                    }
                                } else {
                                    //一般都是存在的2课时以上的
                                    if ($time > $course_scheme[$kk]['date']) {
                                        $progress = $course_scheme[$kk]['schedule']; //当前课时
                                    } elseif ($time < $course_scheme[0]['date']) { //小于开课时间
                                        $progress = 0; //当前课时
                                    } else if ($time > $course_scheme[$date_length - 1]['date']) {  //已经结课
                                        $progress = $date_length; //当前课时
                                    }
                                }
                            }
                        }elseif ($time >$end_lesson_day) {
                                $progress = $date_length;
                            }else{
                                $progress='';
                            }
                        }
                        $data[$k]['progress'] = $progress;

                }
            }else{
                return 0;
            }
        return $data;
    }

    /**
     *统计总人数
     */
    public function total()
    {
        //默认条件
        $where = [
            's.delete_time' => ['=', 0], //未删除
        ];
        if(!empty($where['is_read'])) {

            unset($where['is_read']);
        }
        $data = $this->alias('s')->distinct('s.id')
            ->field('s.*,u.id as uid,u.realname,u.user_login as p_name')
            ->join('user u','s.u_id=u.id','left')
            ->join('class_student ct','ct.student_id=s.id',$type='left')
            ->join('school sc','ct.school_id=sc.id',$type='left')
            ->join('course co','ct.course_id=co.id',$type='left')
            ->where($where)
            ->select();
        if(!$data->isEmpty())
        {
            $data=count($data->toArray());
        }else{
            return 0;
        }
        return $data;
    }
    /**
     * 获取学生列表信息
     * @param array $filter
     * @param array $order
     * @param int $limit
     * @param $query
     * @return \think\Paginator
     */
    public function getStudentsWhere($filter=[])
    {
        //默认条件
        $where = [
            's.delete_time' => ['=', 0], //未删除
        ];
        if(!empty($filter) && is_array($filter)){
            $where = array_merge($where,$filter);
        }
        if(!empty($where['is_read'])) {

            if($where['is_read']==2)
            {
                unset($where['is_read']);
                $count= $this->alias('s')->distinct('s.id')
                    ->field('s.*,u.id as uid,u.realname,u.user_login as p_name,c.class_status')
                    ->join('user u','s.u_id=u.id','left')
                    ->join('class_student ct','ct.student_id=s.id',$type='left')
                    ->join('class c','ct.class_id=c.id',$type='left')
                    ->where($where)
                    ->order("convert(s.name USING gbk) COLLATE gbk_chinese_ci")
                    ->select();
                    if(!$count->isEmpty())
                    {
                        $count=$count->toArray();
                        foreach($count as $k=>$v)
                        {

                            if(!empty($v['class_status'])==1)
                            {

                                unset($count[$k]);
                            }
                        }
                        $count=count($count);
                    }else{
                        $count=0;
                    }
                    return $count;

            }else  if($where['is_read']==1) {
                    unset($where['is_read']);
                    $count = $this->alias('s')->distinct('s.id')
                        ->field('s.*,u.id as uid,u.realname,u.user_login as p_name')
                        ->join('user u', 's.u_id=u.id', 'left')
                        ->join('class_student ct', 'ct.student_id=s.id')
                        ->join('class c', 'ct.class_id=c.id')
                        ->join('school sc', 'ct.school_id=sc.id')
                        ->join('course co', 'ct.course_id=co.id')
                        ->join('order o', 'o.class_id=ct.class_id and c.id=o.class_id and o.student_id=s.id and o.student_id=ct.student_id')
                        ->where('o.order_status', ['eq', 2], ['eq', 1], 'or')
                        ->where($where)
                        ->where('c.class_status', 1)
                        ->select();
                    if (!$count->isEmpty()) {
                        $count = $count->toArray();
                        foreach ($count as $k => $v) {

                            if (!empty($v['class_status']) == 1) {

                                unset($count[$k]);
                            }
                        }
                        $count = count($count);
                    } else {
                        $count = 0;
                    }
                    return $count;
            }
        }

        $data = $this->alias('s')->distinct('s.id')
            ->field('s.*,u.id as uid,u.realname,u.user_login as p_name')
            ->join('user u','s.u_id=u.id','left')
            ->join('class_student ct','ct.student_id=s.id',$type='left')
            ->join('school sc','ct.school_id=sc.id',$type='left')
            ->join('course co','ct.course_id=co.id',$type='left')
            ->where($where)
            ->select();
            if(!$data->isEmpty())
            {
                $data=count($data->toArray());
            }else{
                return 0;
            }
            return $data; //数组可以去掉重复
    }


    /**
     * 生成学号
     * @return string
     */
    public function generateCode()
    {
        $date = date('ymd');
        $new_id = Db::name('student')->where('delete_time','=',0)
            ->whereTime('create_time','today')
            ->count();
        $id = $new_id + 1;
        $length = strlen($id);
        if($length <= 4){
            for($i=0;$i<(4-$length);$i++){
                $id = '0'.$id;
            }
        }
        $code = $date.$id;
        return $code;
    }

    /**
     * 编辑学生
     * @param array $data
     * @return bool
     */
    public function editStudent($data=[])
    {
        $result = true;
        $id = intval($data['id']);

        $old_obj = $this->where('id', $id)->find();
        if(!isset($old_obj->id)){
            $result = false;
        }else{
            self::startTrans();
            try{
                $this->isUpdate(true)->allowField(true)->save($data, ['id' => $id]);

                self::commit();
            }catch(\Exception $e){
                self::rollback();
                $result = false;
            }
        }

        return $result;
    }

    /**
     * 通过字段获取学生
     * @param array $filter
     * @return array|false|\PDOStatement|string|Model
     */
    public function getStudentByField($filter=[])
    {
        //默认条件
        $where = [
            'delete_time' => ['=', 0] //未删除
        ];
        if(!empty($filter) && is_array($filter)){
            $where = array_merge($where,$filter);
        }

        $obj = $this->where($where)->find();

        return $obj;
    }

    /**
     * 删除学生
     * @param $id
     * @param $cur_time
     * @return bool
     */
    public function deleteStudent($id, $cur_time)
    {
        $result = true;
        if(empty($id)){
            $result = false;
        }else{
            if(empty($cur_time)){
                //当前时间
                $cur_time = time();
            }
            if(is_array($id)){
                //批量删除
                $students = $this->where(['id' => ['in', $id]])->select();
                self::startTrans();
                try{
                    $this->where(['id' => ['in', $id]])->update(['delete_time' => $cur_time]);

                    foreach($students as $value){
                        $data = [
                            'object_id'   => $value['id'],
                            'create_time' => $cur_time,
                            'table_name'  => 'student',
                            'name'        => $value['name']
                        ];
                        //扔进垃圾站
                        Db::name('recycleBin')->insert($data);
                    }

                    self::commit();
                }catch(\Exception $e){
                    self::rollback();
                    $result = false;
                }
            }else{
                //单一删除
                self::startTrans();
                try{
                    $res = $this->where(['id' => $id])->find();
                    $data = [
                        'object_id'   => $res['id'],
                        'create_time' => $cur_time,
                        'table_name'  => 'student',
                        'name'        => $res['name']
                    ];
                    $this->where(['id' => $id])->update(['delete_time' => $cur_time]);
                    Db::name('recycleBin')->insert($data);

                    self::commit();
                }catch(\Exception $e){
                    self::rollback();
                    $result = false;
                }
            }
        }

        return $result;
    }

    /**
     * 获取家长的学生
     * @param $parent_id
     * @param int $shop_id
     * @return array|bool|false|\PDOStatement|string|\think\Collection
     */
    public function getStudentsByPid($parent_id=0, $shop_id=1)
    {
       if(empty($parent_id) || !is_numeric($parent_id)){
           return false;
       }
       //默认条件
       $where = [
           'delete_time' => ['=', 0], //未删除
           'shop_id' => ['=', $shop_id], //多店铺
           'u_id' => ['=', $parent_id], //家长
       ];

       //获取家长的学生
       $result = $this->where($where)->order('id','desc')->select();
       if(count($result) <= 0){
           return false;
       }else{
           $result = $result->toArray();
       }

       return $result;
    }

}